package com.mairuide.phone.trackevents;

import java.util.*;
import java.util.regex.Pattern;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.mairuide.phone.callanalysis.entity.PhoneMapDetail;
import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.StringUtils;
import com.mairuide._frame.utils.Testtrans;
import com.mairuide.httpclient.BaseSite;
import com.mairuide.phone.PhoneRecord;

/**
 * 活动轨迹Service
 * @author mh
 *
 */
@Service
@Transactional(readOnly = true)
public class TrackEventsService extends BaseServiceProxy<PhoneRecord> {

	@PersistenceContext
	private EntityManager entityManager;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Resource
	private BaseSite baseSite;

	@Override
	protected EntityManager getEntityManager() {
		return entityManager;
	}

	@Override
	protected BaseRepository<PhoneRecord> getBaseRepository() {
		return null;
	}
	
	/**
	 * 活动轨迹查询
	 * @param eocap
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<TrackEvents> findTrackEvents(TrackEventsParams etp) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT TB_PHONE.*, t2.startTime,t2.endTime\r\n" + 
				"  FROM (SELECT TPR.SERVICE_NUMBER AS serviceNumber,\r\n" + 
				"               COUNT(*) AS callNum,\r\n" + 
				"               COUNT(DISTINCT TPR.LAC || TPR.CID) AS stationNum\r\n" + 
				"          FROM tb_phone_recording TPR\r\n" + 
				"         WHERE TPR.IS_DELETE = 0\r\n" + 
				"           AND TPR.CALL_TYPE IN (0, 1)");
		/* 案件id */
		if(StringUtils.isNotEmpty(etp.getCaseId())) {
			selectSql.append("AND TPR.CASEMANAGE_ID ='"+etp.getCaseId()+"' ");
		}
		/* 主叫号码 */
		selectSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(etp.getCheckedPhone())) {
			/*String phones = "";
			for(String phone : etp.getCheckedPhone().split(",")) {
				if(StringUtils.isNotEmpty(phone)) {
					if(!phone.contains("'")) {
						phones = phones+"'"+phone+"',";
					}
				}
			}
			if(phones.endsWith(",")) {
				phones = phones.substring(0,phones.length()-1);
			}
			if(phones.length()>0) {
				selectSql.append(" OR (TPR.SERVICE_NUMBER IN ("+phones+"))");
			}*/
			selectSql.append(" OR (TPR.SERVICE_NUMBER IN ("+etp.getCheckedPhone()+"))");
		}
		selectSql.append(" ) ");
		/* 通话开始日期 */
		if(StringUtils.isNotEmpty(etp.getStartTime())) {
			selectSql.append(" AND TPR.STARTING_TIME >= to_date('"+etp.getStartTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 通话结束日期 */
		if(StringUtils.isNotEmpty(etp.getEndTime())) {
			selectSql.append(" AND TPR.STARTING_TIME <= to_date('"+etp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		selectSql.append(" GROUP BY TPR.SERVICE_NUMBER) TB_PHONE,(");
		selectSql.append("SELECT T.SERVICE_NUMBER AS serviceNumber,MIN(TO_CHAR(T.STARTING_TIME, 'YYYY-MM-DD')) AS startTime,\r\n" + 
				"                MAX(TO_CHAR(T.STARTING_TIME, 'YYYY-MM-DD')) AS endTime\r\n" + 
				"          FROM TB_PHONE_RECORDING T\r\n" + 
				"         WHERE T.starting_time IS NOT NULL\r\n" + 
				"           AND T.LAC IS NOT NULL\r\n" + 
				"           AND T.CID IS NOT NULL\r\n" + 
				"           AND T.IS_DELETE = 0\r\n" + 
				"           AND T.CALL_TYPE IN (0, 1)");
		/* 主叫号码 */
		selectSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(etp.getCheckedPhone())) {
			/*String phones = "";
			for(String phone : etp.getCheckedPhone().split(",")) {
				if(StringUtils.isNotEmpty(phone)) {
					if(!phone.contains("'")) {
						phones = phones+"'"+phone+"',";
					}
				}
			}
			if(phones.endsWith(",")) {
				phones = phones.substring(0,phones.length()-1);
			}
			if(phones.length()>0) {
				selectSql.append(" OR (T.SERVICE_NUMBER IN ("+phones+"))");
			}*/
			selectSql.append(" OR (T.SERVICE_NUMBER IN ("+etp.getCheckedPhone()+"))");
		}
		selectSql.append(" ) ");
		/* 通话开始日期 */
		if(StringUtils.isNotEmpty(etp.getStartTime())) {
			selectSql.append(" AND T.STARTING_TIME >= to_date('"+etp.getStartTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		/* 通话结束日期 */
		if(StringUtils.isNotEmpty(etp.getEndTime())) {
			selectSql.append(" AND T.STARTING_TIME <= to_date('"+etp.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		
		selectSql.append(" GROUP BY T.SERVICE_NUMBER) t2");
		selectSql.append(" WHERE t2.serviceNumber = TB_PHONE.serviceNumber");
		if(StringUtils.isNotEmpty(etp.getSort())) {
			selectSql.append(" order by "+etp.getSort()+" "+etp.getOrder());
		}
		Query query = getEntityManager().createNativeQuery(selectSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(TrackEvents.class));
		//封装返回数据
		return query.getResultList();
	}

	/**
	 * 活动轨迹查询地图
	 * @param eocap
	 * @return
	 */
	public Map<String, String> findTrackEventsMap(TrackEventsParams etp) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT\r\n" +
				"	LAC,CID,\r\n" +
				"	SERVICE_NUMBER\r\n" +
				"FROM\r\n" +
				"	tb_phone_recording TPR\r\n" +
				"WHERE\r\n" +
				"	TPR.starting_time IS NOT NULL\r\n"+
				"	AND TPR.LAC IS NOT NULL\r\n" +
				"	AND TPR.CID IS NOT NULL\r\n"+
				"   AND TPR.IS_DELETE = 0 AND TPR.CALL_TYPE IN(0,1) ");
		/* 案件id */
		if(StringUtils.isNotEmpty(etp.getCaseId())) {
			selectSql.append("AND TPR.CASEMANAGE_ID ='"+etp.getCaseId()+"' ");
		}
		/* 主叫号码 、被叫号码*/
		selectSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(etp.getCheckedPhone())) {
			String phones = "";
			for(String phone : etp.getCheckedPhone().split(",")) {
				if(StringUtils.isNotEmpty(phone)) {
					if(StringUtils.isNotEmpty(phone)) {
						phones +="'"+ phone+"',";
					}
				}
			}
			if(phones.endsWith(",")) {
				phones = phones.substring(0,phones.length()-1);
			}
			if(phones.length()>0) {
				selectSql.append(" OR (TPR.SERVICE_NUMBER IN ("+phones+")");
				selectSql.append(" AND TPR.PARTY_NUMBER IN ("+phones+"))");
			}
		}
		selectSql.append(" ) ");
		/* 通话开始日期 */
		if(StringUtils.isNotEmpty(etp.getStartTime())) {
			selectSql.append(" AND TPR.STARTING_TIME >= to_date('"+etp.getStartTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}

		/* 通话结束日期 */
		if(StringUtils.isNotEmpty(etp.getEndTime())) {
			selectSql.append(" AND TPR.STARTING_TIME <= to_date('"+etp.getEndTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		selectSql.append(" ORDER BY TO_CHAR(TPR.STARTING_TIME,'YYYY-MM-DD') ");
		List<Map<String, Object>> maps = jdbcTemplate.queryForList(selectSql.toString());
		Map callInfoByIds = null;
		if (org.apache.commons.lang3.StringUtils.isNotBlank(etp.getCheckedPhone())&&maps.size()>0){
			callInfoByIds = baseSite.getCallInfoByIds(BaseSite.getBaseSiteIds(maps));
			List<String> baseSiteList = BaseSite.getBaseSiteList(maps);
			List<Map<String,Object>> data = (List<Map<String,Object>>)callInfoByIds.get("data");
			ArrayList<Map<String,Object>> list1 = new ArrayList<>();
			if (data != null && data.size()>0){
				for (Map<String,Object> m:data){
					list1.add(maps.get(baseSiteList.indexOf((String) m.get("id"))));
				}
			}
			callInfoByIds.put("data2",list1);
			//获取查询结果集
		}
		//封装返回数据
		return callInfoByIds;
	}

	/**
	 * 通话详情查询
	 * @param eocap
	 * @return
	 */
	public Page<PhoneRecord> trackDetails(TrackEventsParams eocap, Pageable page,String sortName,String sortOrder) {
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT\r\n" + 
				"  TPR.RECORD_ID AS recordId,\r\n" + 
				"  TPR.CASEMANAGE_ID AS casemanageId,\r\n" + 
				"  TPR.STARTING_TIME AS startingTime,\r\n" + 
				"  TPR.SERVICE_NUMBER AS serviceNumber,\r\n" + 
				"  TPR.SERVICE_USER_INFORMATION AS serviceUserInformation,\r\n" + 
				"  TPR.SERVICE_LOCATION AS serviceLocation,\r\n" + 
				"  TPR.CARD_NUMBER AS cardNumber,\r\n" + 
				"  TPR.PARTY_NUMBER AS partyNumber,\r\n" + 
				"  TPR.PARTY_USER_INFORMATION AS partyUserInformation,\r\n" + 
				"  TPR.PARTY_LOCATION AS partyLocation,\r\n" + 
				"  TPR.TALK_TIME AS talkTime,\r\n" + 
				"  TPR.CALL_TYPE AS callType,\r\n" + 
				"  TPR.LAC AS lac,\r\n" + 
				"  TPR.CID AS cid,\r\n" + 
				"  TPR.LATB AS latb,\r\n" + 
				"  TPR.LNGB AS lngb,\r\n" + 
				"  TPR.BASE_STATION AS baseStation,\r\n" + 
				"  TPR.MSC AS msc,\r\n" +
				"  TPR.ADDRESS AS address,\r\n" +
				"  TPR.CITY AS city,\r\n" + 
				"  TPR.THIRD_NUMBER AS thirdNumber,\r\n" + 
				"  TPR.THIRD_NUMBER_INFORMATION AS thirdNumberInformation,\r\n" + 
				"  TPR.THIRD_NUMBER_LOCATION AS thirdNumberLocation,\r\n" + 
				"  TPR.OPERATOR_CODE AS operationCode,\r\n" + 
				"  TPR.OPERATOR_CODE_INFORMATION AS operationCodeInformation,\r\n" + 
				"  TPR.BUSINESS_KEY AS businessKey\r\n");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM\r\n" + 
				"  TB_PHONE_RECORDING TPR\r\n" + 
				"WHERE\r\n" + 
				"TPR.IS_DELETE = 0 AND TPR.CALL_TYPE IN(0,1) ");
		
		/* 案件id */
		if(StringUtils.isNotEmpty(eocap.getCaseId())) {
			fromWhereSql.append("AND TPR.CASEMANAGE_ID =? ");
			params.add(eocap.getCaseId());
		}
		if(StringUtils.isNotEmpty(eocap.getServiceNumber())) {
			fromWhereSql.append(" AND TPR.SERVICE_NUMBER = '"+eocap.getServiceNumber()+"' ");
		}
		/* 通话开始日期 */
		if(StringUtils.isNotEmpty(eocap.getStartTime())) {
			fromWhereSql.append(" AND TPR.STARTING_TIME >= to_date('"+eocap.getStartTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}

		/* 通话结束日期 */
		if(StringUtils.isNotEmpty(eocap.getEndTime())) {
			fromWhereSql.append(" AND TPR.STARTING_TIME <= to_date('"+eocap.getEndTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
		}
		if(StringUtils.isNotEmpty(eocap.getMeetTime())) {
			fromWhereSql.append(" AND TO_CHAR (TPR.STARTING_TIME,'yyyy-mm-dd hh24:mi') = '"+eocap.getMeetTime()+"' ");
		}
		if(StringUtils.isNotEmpty(eocap.getCheckedPhone())) {
			fromWhereSql.append(" AND SERVICE_NUMBER in('"+eocap.getCheckedPhone().replace(",","','")+"') ");
		}
		StringBuffer orderBySql = new StringBuffer();
		if(StringUtils.isNotEmpty(sortName)) {
			orderBySql.append(" ORDER BY "+sortName+ " " +sortOrder);
		}else {
			orderBySql.append(" ORDER BY TPR.STARTING_TIME DESC ");
		}
		//封装返回数据
		Page<PhoneRecord> pagePh = super.findPageBySql(PhoneRecord.class,selectSql.toString(), fromWhereSql.toString(), orderBySql.toString(), params, page);
		getAddress(pagePh);
		return pagePh;
	}

	private void getAddress(Page<PhoneRecord> pagePh){
		List<String> phoneCodeList = new ArrayList<String>();
		Set<PhoneMapDetail> phoneMapDetailList = new HashSet<>();
		if(pagePh!=null && pagePh.getContent()!=null) {
			for(PhoneRecord pr:pagePh.getContent()) {
				if(pr.getPartyLocation()!=null && isNumeric(pr.getPartyLocation())) {//判断对方号码归属地是否是电话区号
					String partyLocation = pr.getPartyLocation().trim();
					if(!phoneCodeList.contains(partyLocation)) {
						phoneCodeList.add(partyLocation);
					}
				}
				if(StringUtils.isEmpty(pr.getAddress()) && StringUtils.isNotEmpty(pr.getCid()) && StringUtils.isNotEmpty(pr.getLac())) {
					PhoneMapDetail detail = new PhoneMapDetail();
					detail.setCID(StringUtils.isEmpty(pr.getCid())?"0":pr.getCid());
					detail.setLAC(StringUtils.isEmpty(pr.getLac())?"0":pr.getLac());
					detail.setLATB(StringUtils.isEmpty(pr.getLatb())?"0":pr.getLatb());
					detail.setLNGB(StringUtils.isEmpty(pr.getLngb())?"0":pr.getLngb());
					detail.setADDRESS(StringUtils.isEmpty(pr.getAddress())?"0":pr.getAddress());
					detail.setSERVICE_NUMBER(pr.getServiceNumber());
					phoneMapDetailList.add(detail);
				}
			}
		}
		if(phoneCodeList.size()>0) {
			Map<String, String> phoneCodeMap = getLocationByPhoneCode(phoneCodeList);
			for(PhoneRecord pr:pagePh.getContent()) {
				if(pr.getPartyLocation()!=null && isNumeric(pr.getPartyLocation())) {//判断对方号码归属地是否是电话区号
					String partyLocation = pr.getPartyLocation().trim();
					if(phoneCodeMap.get(partyLocation)!=null) {
						pr.setPartyLocation(phoneCodeMap.get(partyLocation).toString());
					}
				}
			}
		}
		List<PhoneMapDetail> arrayList = new ArrayList<>(phoneMapDetailList);
		if(arrayList.size()>0) {
			List arrayList_hava = baseSite.getMap(arrayList);
			if(arrayList_hava!=null && arrayList_hava.size()>0) {
				for(PhoneMapDetail detail:(List<PhoneMapDetail>)arrayList_hava) {
					for(PhoneRecord pr:pagePh.getContent()) {
						if(StringUtils.isEmpty(pr.getAddress()) && StringUtils.isNotEmpty(pr.getCid()) && StringUtils.isNotEmpty(pr.getLac())) {
							if(pr.getCid().equals(detail.getCID()) && pr.getLac().equals(detail.getLAC())) {
								pr.setAddress(detail.getADDRESS());
							}
						}
					}
				}
			}
		}
	}
	private boolean isNumeric(String str){
		Pattern pattern = Pattern.compile("[0-9]*");
		return pattern.matcher(str).matches();
	}

	/**
	 * 根据电话区号，获取服务地
	 * @return
	 */
	public Map<String, String> getLocationByPhoneCode(List<String> phoneCodeList){
		Map<String, String> result = new HashMap<String, String>();
		String sql="select distinct FULL_NAME,PHONE_CODE from l_zip_district_phonecode where 1=1 and phone_code in (";
		for(String phoneCode:phoneCodeList) {
			sql+="'"+phoneCode+"',";
		}
		sql = sql.substring(0,sql.length()-1);
		sql+=")";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		if(list!=null && list.size()>0) {
			for(Map<String, Object> map :list) {
				String phoneCode = map.get("PHONE_CODE")==null?"":map.get("PHONE_CODE").toString();
				String fullName = map.get("FULL_NAME")==null?"":map.get("FULL_NAME").toString();
				result.put(phoneCode, fullName);
			}
		}
		return result;
	}
}

